#ifndef FILE_CONTROLLER_H
#define FILE_CONTROLLER_H

#include <QMainWindow>
#include <QList>
#include <QString>
#include <QStringList>
#include <QMessageBox>
#include <QTableView>
#include <QApplication>
#include <QFileDialog>
#include <QFile>
#include <QSqlTableModel>
#include <QSqlRecord>
#include <QSqlField>
#include <QStringList>
#include <QDesktopServices>
#include <QUrl>
#include <QListWidget>
#include <QListWidgetItem>
#include <QDateTime>
#include <QColor>
#include <QTextEdit>
#include "xlsxformat.h"
#include "sql_adapter.h"
#include "xlsxdocument.h"
#include "xlsxchartsheet.h"
#include "xlsxcellrange.h"
#include "xlsxchart.h"
#include "xlsxrichstring.h"
#include "xlsxworkbook.h"
#include "setting.h"

void show_msg_box(QString tip);

class columns_index
{
public:
    QString name;
    QString kind;
    QString length;
    bool is_primary;
    bool is_null;
    bool is_unique;

    columns_index(QString _name, QString _kind, QString _length, bool _pri, bool _null, bool _uni)
    {
        name = _name;
        kind = _kind;
        length = _length;
        is_primary = _pri;
        is_null = _null;
        is_unique = _uni;
    }
};

enum file_kind
{
    csv,
    xls,
    xlsx,
    og_db
};

void write_csv(QString dir, QSqlTableModel* model, QString table_name, QTableView* table)
{
    QString file_location = dir + "/" + table_name + ".csv";
    QFile file(file_location);

    if(QFile::exists(file_location))
    {
        show_msg_box("文件" + table_name + ".csv已存在");
    }
    else
    {
        if(file.open(QIODevice::ReadWrite))
        {
            //获得列名
            QString sql = "SELECT column_name FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = \'" + table_name + "\'";
            start_sql_query(sql);

            QString temp = "";
            while (db_helper -> next())
            {
                temp = temp + db_helper -> value(0).toString() + " \t";
            }
            temp = temp + "\n";
            file.write(temp.toUtf8());

            db_helper -> clear();

            //write data
            for(int i = 0; i < model -> rowCount(); i++)
            {
                QString temp = "";
                for(int j = 0; j < model -> columnCount(); j++)
                {
                    QString index_str = model -> data(model -> index(i, j)).toString();
                    temp = temp + index_str;

                    if(j != model -> columnCount() - 1)
                    {
                        temp = temp + "\t";
                    }
                }
                temp = temp + "\n";

                file.write(temp.toUtf8());
            }
        }
        else
        {
            QMessageBox msg;
            msg.setText("文件创建失败");
            msg.exec();
        }
        file.close();
    }
}

void write_xls(QString dir, QSqlTableModel* model, QString table_name, QTableView* table)
{
    QString file_location = dir + "/" + table_name + ".xls";

    if(QFile::exists(file_location))
    {
        QMessageBox msg;
        msg.setText("文件" + table_name + ".xls已存在");
        msg.exec();
    }
    else
    {
        //获得列名
        QString sql = "SELECT column_name FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = \'" + table_name + "\'";
        start_sql_query(sql);

        QXlsx::Document temp;
        temp.saveAs(file_location);
        temp.deleteLater();

        QXlsx::Document xls(file_location);
        QXlsx::Workbook *workBook = xls.workbook();
        QXlsx::Worksheet *workSheet = static_cast<QXlsx::Worksheet*>(workBook->sheet(0));

        QXlsx::Format header_format;
        header_format.setPatternBackgroundColor(QColor("#696969"));
        header_format.setVerticalAlignment(QXlsx::Format::AlignVCenter);
        header_format.setHorizontalAlignment(QXlsx::Format::AlignHCenter);
        header_format.setFontColor(QColor("#FFFFFF"));
        header_format.setFontSize(13);
        header_format.setFontBold(true);

        int colu = 1;
        while (db_helper -> next())
        {
            workSheet -> write(1, colu, db_helper -> value(0).toString(), header_format);
            colu++;
        }
        xls.save();

        db_helper -> clear();

        //write data
        int row = 2;
        colu = 1;
        for(int i = 0; i < model -> rowCount(); i++)
        {
            for(int j = 0; j < model -> columnCount(); j++)
            {
                workSheet -> write(row, colu, model -> data(model -> index(i, j)).toString());
                colu++;
            }
            row++;
            colu = 1;
            xls.save();
        }
        xls.deleteLater();
    }
}

void write_xlsx(QString dir, QSqlTableModel* model, QString table_name, QTableView* table)
{
    QString file_location = dir + "/" + table_name + ".xlsx";

    if(QFile::exists(file_location))
    {
        QMessageBox msg;
        msg.setText("文件" + table_name + ".xlsx已存在");
        msg.exec();
    }
    else
    {
        //获得列名
        QString sql = "SELECT column_name FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = \'" + table_name + "\'";
        start_sql_query(sql);

        QXlsx::Document temp;
        temp.saveAs(file_location);
        temp.deleteLater();

        QXlsx::Document xls(file_location);
        QXlsx::Workbook *workBook = xls.workbook();
        QXlsx::Worksheet *workSheet = static_cast<QXlsx::Worksheet*>(workBook->sheet(0));

        QXlsx::Format header_format;
        header_format.setPatternBackgroundColor(QColor("#696969"));
        header_format.setVerticalAlignment(QXlsx::Format::AlignVCenter);
        header_format.setHorizontalAlignment(QXlsx::Format::AlignHCenter);
        header_format.setFontColor(QColor("#FFFFFF"));
        header_format.setFontSize(13);
        header_format.setFontBold(true);

        int colu = 1;
        while (db_helper -> next())
        {
            workSheet -> write(1, colu, db_helper -> value(0).toString(), header_format);
            colu++;
        }
        xls.save();

        db_helper -> clear();

        //write data
        int row = 2;
        colu = 1;
        for(int i = 0; i < model -> rowCount(); i++)
        {
            for(int j = 0; j < model -> columnCount(); j++)
            {
                workSheet -> write(row, colu, model -> data(model -> index(i, j)).toString());
                colu++;
            }
            row++;
            colu = 1;
            xls.save();
        }
        xls.deleteLater();
    }
}

void write_og_db(QString dir, QSqlTableModel* model, QString table_name, QTableView* table)
{
    QString file_location = dir + "/" + table_name + ".og_db";
    QFile file(file_location);

    if(QFile::exists(file_location))
    {
        QMessageBox msg;
        msg.setText("文件" + table_name + ".og_db已存在");
        msg.exec();
    }
    else
    {
        if(file.open(QIODevice::ReadWrite))
        {
            QList<columns_index> column_list;

            //获得列名 是否非空 列属性 字符串长度
            QString sql = "SELECT column_name,is_nullable,data_type,character_maximum_length FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = \'" + table_name + "\'";
            start_sql_query(sql);

            QSqlRecord rec = db_helper -> record();
            int columns = rec.count();

            while (db_helper -> next())
            {
                QString column_name;
                bool is_null;
                QString data_type;
                QString length;

                column_name = db_helper -> value(0).toString();
                is_null = db_helper -> value(1).toBool();
                data_type = db_helper -> value(2).toString();
                length = db_helper -> value(3).toString();

                columns_index* temp = new columns_index(column_name, data_type, length, false, is_null, false);
                column_list.append(*temp);
            }

            db_helper -> clear();

            //pkey是主键 列名_key是unique
            sql = "SELECT constraint_name,column_name FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE table_name = \'" + table_name + "\'";
            start_sql_query(sql);

            rec = db_helper -> record();
            columns = rec.count();

            while (db_helper -> next())
            {
                QString constraint = db_helper -> value(0).toString();

                if(constraint.split('_').last() == "pkey")
                {
                    for(int i = 0; i < column_list.count(); i++)
                    {
                        if(column_list[i].name == db_helper -> value(1).toString())
                        {
                            column_list[i].is_primary = true;
                            break;
                        }
                    }
                }
                else if(constraint.split('_').last() == "key")
                {
                    for(int i = 0; i < column_list.count(); i++)
                    {
                        if(column_list[i].name == db_helper -> value(1).toString())
                        {
                            column_list[i].is_unique = true;
                            break;
                        }
                    }
                }
                else if(constraint.split('_').last() == "fkey")
                {

                }
            }

            db_helper -> clear();

            //write table name
            QString name_temp = "table_name: " + table_name + "\n";
            file.write(name_temp.toUtf8());

            //write column num
            QString columns_number = "columns_nums: " + QString::number(column_list.count()) + "\n";
            file.write(columns_number.toUtf8());

            //write frame
            for(int i = 0; i < column_list.count(); i++)
            {
                QString temp;
                temp = column_list[i].name + " ";

                if(column_list[i].length == "0")
                {
                    temp = temp + column_list[i].kind;
                }
                else
                {
                    temp = temp + column_list[i].kind + "(" + column_list[i].length + ")";
                }

                if(column_list[i].is_primary)
                {
                    temp = temp + " PRIMARY_KEY" + "\n";
                }
                else
                {
                    if(column_list[i].is_unique)
                    {
                        temp = temp + " UNIQUE" + "\n";
                    }
                    else if(column_list[i].is_null == false)
                    {
                        temp = temp + " NOT_NULL" + "\n";
                    }
                    else
                    {
                        temp = temp + " \n";
                    }
                }

                file.write(temp.toUtf8());
            }

            //write data
            QString rows_number = "row_count: " + QString::number(model -> rowCount()) + "\n";
            file.write(rows_number.toUtf8());
            for(int i = 0; i < model -> rowCount(); i++)
            {
                QString temp = "";
                for(int j = 0; j < model -> columnCount(); j++)
                {
                    QString index_str = model -> data(model -> index(i, j)).toString();
                    if(index_str.isEmpty())
                    {
                        temp = temp + "empty index";
                    }
                    else
                    {
                        temp = temp + index_str;
                    }

                    if(j != model -> columnCount() - 1)
                    {
                        temp = temp + " \t";
                    }
                }
                temp = temp + "\n";

                file.write(temp.toUtf8());
            }
        }
        else
        {
            QMessageBox msg;
            msg.setText("文件创建失败");
            msg.exec();
        }
        file.close();
    }
}

void write_file(QString table_name, QSqlTableModel* model, QTableView* table, file_kind kind)
{
    QString dir_location = QFileDialog::getExistingDirectory(NULL, "选择文件保存路径", ".");

    switch(kind)
    {
    case csv:
        write_csv(dir_location, model, table_name, table);
        break;
    case xls:
        write_xls(dir_location, model, table_name, table);
        break;
    case xlsx:
        write_xlsx(dir_location, model, table_name, table);
        break;
    case og_db:
        write_og_db(dir_location, model, table_name, table);
        break;
    }

    if(setting -> open_dir_after_create_file)
    {
        QDesktopServices::openUrl(QUrl::fromLocalFile(dir_location));
    }
}

void read_og_db(QString path, QListWidget* table_list)
{
    QFile file(path);
    file.open(QIODevice::ReadOnly);

    //read name
    QString table_name = file.readLine().split(' ')[1];
    table_name = table_name.remove('\n');

    if(get_all_tables().contains(table_name))
    {
        QMessageBox msg;
        msg.setText("数据库内存在同名表");
        msg.exec();
        return;
    }

    //read column
    int column_num = file.readLine().split(' ')[1].toInt();
    QStringList primary_keys;
    QList<columns_index>* new_columns = new QList<columns_index>();

    for(int i = 0; i < column_num; i++)
    {
        QString temp_str = file.readLine();
        QStringList temp = temp_str.split(' ');

        QString name = temp[0];
        QString data_type = temp[1];
        bool is_pri = false;
        bool nullable = true;
        bool unique = false;
        if(temp[2].contains("PRIMARY_KEY"))
        {
            primary_keys.append(temp[0]);
            is_pri = true;
        }
        else if(temp[2] == "UNIQUE")
        {
            unique = true;
        }
        else if(temp[2] == "NOT_NULL")
        {
            nullable = false;
        }

        columns_index* temp_column = new columns_index(name, data_type, 0, is_pri, nullable, unique);
        new_columns -> append(*temp_column);
    }

    //write sql
    QString sql_str = "CREATE TABLE " + table_name + "(";
    if(primary_keys.count() == 1)
    {
        for(int i = 0; i < new_columns -> count(); i++)
        {
            sql_str = sql_str + new_columns -> at(i).name + " " + new_columns -> at(i).kind;
            if(new_columns -> at(i).is_primary)
            {
                sql_str = sql_str + " PRIMARY KEY";
            }
            else if(new_columns -> at(i).is_null == false)
            {
                sql_str = sql_str + " NOT NULL";
            }
            else if(new_columns -> at(i).is_unique)
            {
                sql_str = sql_str + " UNIQUE";
            }

            if(i != new_columns -> count() - 1)
            {
                sql_str = sql_str + ",";
            }
        }
        sql_str = sql_str + ");";
    }
    else
    {
        for(int i = 0; i < new_columns -> count(); i++)
        {
            sql_str = sql_str + new_columns -> at(i).name + " " + new_columns -> at(i).kind;
            if(new_columns -> at(i).is_null == false)
            {
                sql_str = sql_str + " NOT NULL,";
            }
            else if(new_columns -> at(i).is_unique)
            {
                sql_str = sql_str + " UNIQUE,";
            }
            else
            {
                sql_str = sql_str + ",";
            }
        }

        sql_str = sql_str + "PRIMARY KEY(";
        for(int i = 0; i < primary_keys.count(); i++)
        {
            sql_str = sql_str + primary_keys[i];
            if(i != primary_keys.count() - 1)
            {
                sql_str = sql_str + ",";
            }
        }

        sql_str = sql_str + "));";
    }
    start_sql_query(sql_str);
    db_helper -> clear();

    //write data
    QSqlTableModel* model = new QSqlTableModel(nullptr, db);
    model -> setTable(table_name);
    model -> setEditStrategy(QSqlTableModel::OnManualSubmit);
    model -> select();

    int row_count = file.readLine().split(' ')[1].toInt();

    for(int i = 0; i < row_count; i++)
    {
        QString temp = file.readLine();
        QStringList temp_data = temp.split('\t');

        model -> insertRow(model -> rowCount());
        for(int j = 0; j < temp_data.count(); j++)
        {
            model -> setData(model -> index(model -> rowCount() - 1, j), temp_data[j]);
        }
        model -> submitAll();
        model -> select();
    }

    model -> deleteLater();

    /*int row_count = file.readLine().split(' ')[1].toInt();

    for(int i = 0; i < row_count; i++)
    {
        QString temp = file.readLine();
        QStringList temp_data = temp.split('\t');
        QString temp_sql = "INSERT INTO " + table_name + " VALUES(";

        for(int j = 0; j < temp_data.count(); j++)
        {
            temp_sql = temp_sql + temp_data[j];
            if(j != temp_data.count() - 1)
            {
                temp_sql = temp_sql + ',';
            }
            else
            {
                temp_sql = temp_sql + ')';
            }
        }

        start_sql_query(temp_sql);
        db_helper -> clear();
    }*/

    table_list -> addItem(table_name);

    //close file
    file.close();
}

void read_file(file_kind kind, QListWidget* table_list)
{
    QString file_location = QFileDialog::getOpenFileName(nullptr, "选择文件", ".", "*.og_db");

    switch(kind)
    {
    case og_db:
        read_og_db(file_location, table_list);
        break;
    }
}

void backup_table(QSqlTableModel* model, QString table_name)
{
    QDir backup_dir(setting -> backup_dir);
    if(backup_dir.exists() == false)
    {
        backup_dir.mkdir(setting -> backup_dir);
    }

    QString file_location = setting -> backup_dir + "/" + table_name + " " + QDateTime::currentDateTime().toString("yyyy-MM-dd hh:mm:ss") + ".xlsx";

    if(QFile::exists(file_location))
    {
        QMessageBox msg;
        msg.setText("文件" + table_name + ".xlsx已存在");
        msg.exec();
    }
    else
    {
        //获得列名
        QString sql = "SELECT column_name FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = \'" + table_name + "\'";
        start_sql_query(sql);

        QXlsx::Document temp;
        temp.saveAs(file_location);
        temp.deleteLater();

        QXlsx::Document xls(file_location);
        QXlsx::Workbook *workBook = xls.workbook();
        QXlsx::Worksheet *workSheet = static_cast<QXlsx::Worksheet*>(workBook->sheet(0));

        QXlsx::Format header_format;
        header_format.setPatternBackgroundColor(QColor("#696969"));
        header_format.setVerticalAlignment(QXlsx::Format::AlignVCenter);
        header_format.setHorizontalAlignment(QXlsx::Format::AlignHCenter);
        header_format.setFontColor(QColor("#FFFFFF"));
        header_format.setFontSize(13);
        header_format.setFontBold(true);

        int colu = 1;
        while (db_helper -> next())
        {
            workSheet -> write(1, colu, db_helper -> value(0).toString(), header_format);
            colu++;
        }
        xls.save();

        db_helper -> clear();

        //write data
        int row = 2;
        colu = 1;
        for(int i = 0; i < model -> rowCount(); i++)
        {
            for(int j = 0; j < model -> columnCount(); j++)
            {
                workSheet -> write(row, colu, model -> data(model -> index(i, j)).toString());
                colu++;
            }
            row++;
            colu = 1;
            xls.save();
        }
        xls.deleteLater();
    }
}

QString WriteSqlFile(QTextEdit* SqlEdit, bool NewSqlFile, QString FilePath = "null")
{
    if(NewSqlFile)
    {
        QString Dir = QFileDialog::getSaveFileName(NULL, "选择SQL文件保存位置", "", "SQL(*.sql)");

        if(Dir.isEmpty() == false)
        {
            Dir = Dir + ".sql";

            QFile SqlFile(Dir);
            if(SqlFile.exists())
            {
                QMessageBox msg;
                msg.setText("文件" + Dir + "已存在");
                msg.exec();
            }
            else
            {
                if(SqlFile.open(QIODevice::WriteOnly))
                {
                    SqlFile.write(SqlEdit -> toPlainText().toUtf8());
                }
                else
                {
                    QMessageBox msg;
                    msg.setText("文件" + Dir + "无法写入");
                    msg.exec();
                }
            }
            SqlFile.close();

            return Dir;
        }

        return "";
    }
    else
    {
        QFile SqlFile(FilePath);

        if(SqlFile.open(QIODevice::WriteOnly))
        {
            SqlFile.resize(0);
            SqlFile.write(SqlEdit -> toPlainText().toUtf8());
        }
        else
        {
            QMessageBox msg;
            msg.setText("文件" + FilePath + "无法写入");
            msg.exec();
        }

        SqlFile.close();

        return FilePath;
    }
}

#endif // FILE_CONTROLLER_H
